Release 10.1A: OpenEdge Data Management:
SQL Reference

WITH clause

Enables table-level locking when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.

Syntax

The locking hint clause, such as for READPAST, can only be specified in the main SELECT statement, but not in the subquery SELECT statement in the “search condition” of the WHERE clause, as shown in the following syntax:

[ WITH ( READPAST [ WAIT timeout | NOWAIT ] ) ]

search_condition

The READPAST locking hint skips locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.

WAIT timeout

Override the default lock-wait time out. The timeout value is in seconds and can be 0 or any positive number.

NOWAIT

Causes the SELECT statement to skip (read past) the row immediately if a lock cannot be acquired on a row in the selection set because of the lock held by some other transaction. The default behavior is for the transaction to wait until it gets the required lock or until it times out waiting for the lock.

Example

SELECT * FROM Customer WHERE “CustNum” < 100 ORDER BY “CustNum” FOR UPDATE WITH (READPAST WAIT 1);